﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_BILLSLEDGER_BU_BLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT *
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY T1.N_ID desc) as r,
               T1.N_ID,
               DECODE(T1.N_TYPE,1,'银行汇票',2,'银行本票',3,'转账支票',4,'现金支票',5,'银行承兑汇票',6,'商业承兑汇票') N_TYPE,
               T1.C_BILLCODE,
               T1.C_DRAWERCMYNAME,
               T1.C_DRAWERBANKNAME,
               T1.C_DRAWERACCOUNT,
               T1.N_DRAWERCMYID,
               T1.C_DRAWEENAME,
               T1.C_PAYEECMYNAME,
               T1.C_PAYEEBANKNAME,
               T1.C_PAYEEACCOUNT,
               T1.N_PAYEECMYID,
               T1.C_PAYEENUMBER,
               T1.N_INVOICEVALUE,
               to_char(T1.D_STARTDATE, 'yyyy-mm-dd') D_STARTDATE,
               to_char(T1.D_ENDDATE, 'yyyy-mm-dd') D_ENDDATE,
               T1.N_TERMOFBILL,
               to_char(T1.D_ACCEPTANCETIME, 'yyyy-mm-dd') D_ACCEPTANCETIME,
               to_char(T1.D_COLLECTTICKETSTIME, 'yyyy-mm-dd') D_COLLECTTICKETSTIME,
               T1.C_PROJECTNAME,
               T1.C_PROJECTCODE,
               T1.N_IFOFFER,
               T1.N_PAYTYPE,
               T1.N_STATE,
               T1.N_ENTRYSTAFF,
               T1.N_SUM,
               T1.C_REMARK,
               T2.USER_COMPANY AS CMYID,
               T2.USER_NAME AS USERNAME
          FROM YX_BILLSLEDGER_BU T1, SYS_USER T2
         WHERE T2.USER_ID = T1.N_ENTRYSTAFF ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT COUNT(1)
  FROM YX_BILLSLEDGER_BU T1, SYS_USER T2
 WHERE  T2.USER_ID = T1.N_ENTRYSTAFF AND T2.USER_COMPANY=1 ");
            int count = GetCount(sql);
            return count;
        }

        /// <summary>
        /// 根据编号查询银行汇票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_BILLSLEDGER_BU SelectById(int id)
        {
            var sql = "SELECT * FROM YX_BILLSLEDGER_BU T1 WHERE T1.N_ID=" + id;
            var dt = Query(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            if (dt.Rows.Count > 1)
            {
                throw new Exception("数据异常");
            }

            var entity = new YX_BILLSLEDGER_BU();
            var row = dt.Rows[0];

            entity.N_ID = Convert.ToDecimal(row["N_ID"]);
            entity.N_TYPE = Convert.ToDecimal(row["N_TYPE"]);
            entity.C_BILLCODE = Convert.ToString(row["C_BILLCODE"]);
            entity.C_DRAWERCMYNAME = Convert.ToString(row["C_DRAWERCMYNAME"]);
            entity.C_DRAWERBANKNAME = Convert.ToString(row["C_DRAWERBANKNAME"]);
            entity.C_DRAWERACCOUNT = Convert.ToString(row["C_DRAWERACCOUNT"]);
            entity.N_DRAWERCMYID = Convert.ToDecimal(row["N_DRAWERCMYID"]);
            entity.C_DRAWEENAME = Convert.ToString(row["C_DRAWEENAME"]);
            entity.C_PAYEEACCOUNT = Convert.ToString(row["C_PAYEEACCOUNT"]);
            entity.C_PAYEEBANKNAME = Convert.ToString(row["C_PAYEEBANKNAME"]);
            entity.C_PAYEECMYNAME = Convert.ToString(row["C_PAYEECMYNAME"]);
            entity.N_PAYEECMYID = Convert.ToDecimal(row["N_PAYEECMYID"]);
            entity.C_PAYEENUMBER = Convert.ToString(row["C_PAYEENUMBER"]);
            entity.N_INVOICEVALUE = System.Decimal.Round(Convert.ToDecimal(row["N_INVOICEVALUE"]), 4);//Convert.ToInt32(row["N_INVOICEVALUE"]);
            entity.D_STARTDATE = Convert.ToDateTime(row["D_STARTDATE"]);
            entity.D_ENDDATE = Convert.ToDateTime(row["D_ENDDATE"]);
            entity.N_TERMOFBILL = Convert.ToInt32(row["N_TERMOFBILL"]);
            entity.D_ACCEPTANCETIME = Convert.ToDateTime(row["D_ACCEPTANCETIME"]);
            entity.D_COLLECTTICKETSTIME = Convert.ToDateTime(row["D_COLLECTTICKETSTIME"]);
            entity.C_PROJECTCODE = Convert.ToString(row["C_PROJECTCODE"]);
            entity.C_PROJECTNAME = Convert.ToString(row["C_PROJECTNAME"]);
            entity.N_IFOFFER = Convert.ToInt32(row["N_IFOFFER"]);
            entity.N_PAYTYPE = Convert.ToInt32(row["N_PAYTYPE"]);
            entity.N_STATE = Convert.ToInt32(row["N_STATE"]);
            entity.N_ENTRYSTAFF = Convert.ToInt32(row["N_ENTRYSTAFF"]);
            entity.N_SUM = Convert.ToInt32(row["N_SUM"]);
            entity.C_REMARK = Convert.ToString(row["C_REMARK"]);
            return entity;
        }

        public bool UPDATE(YX_BILLSLEDGER_BU model, string operName)
        {
            string sql = string.Format(@"update yx_billsledger_bu
   set n_type = {0},
       c_billcode = '{1}',
       c_drawercmyname = '{2}',
       c_drawerbankname = '{3}',
       c_draweraccount = '{4}',
       N_DRAWERCMYID = {5},
       c_draweename = '{6}',
       c_payeecmyname = '{7}',
       c_payeebankname = '{8}',
       c_payeeaccount = '{9}',
       N_PAYEECMYID = {10},
       c_payeenumber = '{11}',
       n_invoicevalue = {12},
       d_startdate = TO_DATE('{13}','YYYY-MM-DD HH24:MI:SS'),
       d_enddate = TO_DATE('{14}','YYYY-MM-DD HH24:MI:SS'),
       n_termofbill = {15},
       d_acceptancetime = TO_DATE('{16}','YYYY-MM-DD HH24:MI:SS'),
       d_collectticketstime = TO_DATE('{17}','YYYY-MM-DD HH24:MI:SS'),
       c_projectname = '{18}',
       c_projectcode = '{19}',
       n_ifoffer = {20},
       n_paytype = {21},
       n_state = {22},
       n_entrystaff = {23},
       n_sum={24},
       c_remark = '{25}'
 where n_id = {26}
", model.N_TYPE, model.C_BILLCODE, model.C_DRAWERCMYNAME, model.C_DRAWERBANKNAME, model.C_DRAWERACCOUNT, model.N_DRAWERCMYID, model.C_DRAWEENAME,
 model.C_PAYEECMYNAME, model.C_PAYEEBANKNAME, model.C_PAYEEACCOUNT, model.N_PAYEECMYID, model.C_PAYEENUMBER, model.N_INVOICEVALUE,
 model.D_STARTDATE, model.D_ENDDATE, model.N_TERMOFBILL, model.D_ACCEPTANCETIME, model.D_COLLECTTICKETSTIME, model.C_PROJECTNAME,
 model.C_PROJECTCODE, model.N_IFOFFER, model.N_PAYTYPE, model.N_STATE, model.N_ENTRYSTAFF, model.N_SUM, model.C_REMARK, model.N_ID);
            int count = NoQuery(sql, operName);
            bool istrue = false;
            if (count > 0)
            {
                istrue = true;
            }
            return istrue;
        }

        public bool Insert(YX_BILLSLEDGER_BU entity, string operName)
        {
            entity.N_ID = GetSequence("SEQ_BILLSLEDGER_BU_ID");
            string sql = string.Format(@"insert into yx_billsledger_bu
  (n_id,
   n_type,
   c_billcode,
   c_drawercmyname,
   c_drawerbankname,
   c_draweraccount,
   N_DRAWERCMYID,
   c_draweename,
   c_payeecmyname,
   c_payeebankname,
   c_payeeaccount,
   N_PAYEECMYID,
   c_payeenumber,
   n_invoicevalue,
   d_startdate,
   d_enddate,
   n_termofbill,
   d_acceptancetime,
   d_collectticketstime,
   c_projectname,
   c_projectcode,
   n_ifoffer,
   n_paytype,
   n_state,
   n_entrystaff,
   n_sum,
   c_remark)
values
  ({0},
   {1},
   '{2}',
   '{3}',
   '{4}',
   '{5}',
   '{6}',
   '{7}',
   '{8}',
   '{9}',
   '{10}',
   '{11}',
   '{12}',
   {13},
   TO_DATE('{14}','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('{15}','YYYY-MM-DD HH24:MI:SS'),
   {16},
   TO_DATE('{17}','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('{18}','YYYY-MM-DD HH24:MI:SS'),
   '{19}',
   '{20}',
   {21},
   {22},
   {23},
   {24},
   {25},
   '{26}')", entity.N_ID, entity.N_TYPE, entity.C_BILLCODE, entity.C_DRAWERCMYNAME, entity.C_DRAWERBANKNAME, entity.C_DRAWERACCOUNT, entity.N_DRAWERCMYID,
           entity.C_DRAWEENAME, entity.C_PAYEECMYNAME, entity.C_PAYEEBANKNAME, entity.C_PAYEEACCOUNT, entity.N_PAYEECMYID, entity.C_PAYEENUMBER,
           entity.N_INVOICEVALUE, entity.D_STARTDATE, entity.D_ENDDATE, entity.N_TERMOFBILL, entity.D_ACCEPTANCETIME, entity.D_COLLECTTICKETSTIME,
           entity.C_PROJECTNAME, entity.C_PROJECTCODE, entity.N_IFOFFER, entity.N_PAYTYPE, entity.N_STATE, entity.N_ENTRYSTAFF, entity.N_SUM, entity.C_REMARK);
            int count = NoQuery(sql, operName);
            bool istrue = false;
            if (count > 0)
            {
                istrue = true;
            }
            return istrue;
        }
    }
}